import sqlite3
from pathlib import Path


class SQLitePathCleaner:
    def __init__(self, db_path):
        """初始化数据库连接路径"""
        self.db_path = db_path

    def normalize_path(self, path):
        """规范化路径：将反斜杠转换为正斜杠"""
        if not path:
            return path
        return path.replace("\\", "/")  # 将所有反斜杠转换为正斜杠

    def remove_static_prefix(self, path):
        """清除路径前的 'static/' 前缀，并规范化路径"""
        normalized_path = self.normalize_path(path)

        # 仅当路径以 'static/' 开头时才处理
        if normalized_path.startswith("static/"):
            return normalized_path[len("static/") :]  # 截取 'static/' 之后的部分
        return normalized_path

    def clean_paths(self):
        """批量处理两个表中的路径字段"""
        conn = None
        try:
            # 1. 连接数据库
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()

            # 2. 处理 product_details 表的 local_image_path 字段
            cursor.execute(
                "SELECT id, local_image_path FROM product_details WHERE local_image_path IS NOT NULL"
            )
            details = cursor.fetchall()
            updated_count = 0
            for detail_id, old_path in details:
                new_path = self.remove_static_prefix(old_path)
                if new_path != old_path:  # 只有路径变化时才更新
                    cursor.execute(
                        "UPDATE product_details SET local_image_path = ? WHERE id = ?",
                        (new_path, detail_id),
                    )
                    updated_count += 1
            print(f"处理完成：product_details 表共更新 {updated_count} 条记录")

            # 3. 处理 image_records 表的 local_path 字段
            cursor.execute("SELECT id, local_path FROM image_records")
            records = cursor.fetchall()
            updated_count = 0
            for record_id, old_path in records:
                new_path = self.remove_static_prefix(old_path)
                if new_path != old_path:  # 只有路径变化时才更新
                    cursor.execute(
                        "UPDATE image_records SET local_path = ? WHERE id = ?",
                        (new_path, record_id),
                    )
                    updated_count += 1
            print(f"处理完成：image_records 表共更新 {updated_count} 条记录")

            # 4. 提交事务
            conn.commit()
            print("所有路径处理完成，已提交到数据库")

        except sqlite3.Error as e:
            print(f"数据库操作错误：{e}")
            if conn:
                conn.rollback()  # 出错时回滚事务
        finally:
            if conn:
                conn.close()  # 确保连接关闭


def update_product_descriptions(db_path):
    try:
        # 连接到数据库
        with sqlite3.connect(str(db_path)) as conn:
            cursor = conn.cursor()

            # 开始事务
            conn.execute("BEGIN TRANSACTION")

            # 执行更新操作
            update_query = """
            UPDATE product_details
            SET description = other_model,
                description_en = other_model
            WHERE other_model IS NOT NULL;
            """
            cursor.execute(update_query)

            # 提交事务
            conn.execute("COMMIT")

            # 获取更新的记录数
            updated_count = cursor.rowcount
            print(f"成功更新 {updated_count} 条记录")

    except sqlite3.Error as e:
        print(f"数据库操作出错: {e}")
        # 发生错误时自动回滚（with语句会处理）
    except Exception as e:
        print(f"发生未知错误: {e}")


# 执行示例
if __name__ == "__main__":
    main_path = Path(__file__).parent
    db_path = main_path / "instance" / "data.db"
    # print(main_path)
    # print(db_path)

    # 初始化工具并执行
    cleaner = SQLitePathCleaner(str(db_path))
    cleaner.clean_paths()

    # update_product_descriptions(db_path)
